package bp.sys.printer;

import bp.da.*;
import bp.sys.*;
import bp.sys.printer.DBModel.DB;
import bp.sys.printer.DBModel.DBDtl;
import bp.sys.printer.DBModel.DBDtls;
import bp.sys.printer.DBModel.DBEles;
import bp.sys.printer.Sdt.SDTContentControl;
import bp.sys.printer.Sdt.WordFillContentControls;
import bp.wf.WorkSysFieldAttr;
import bp.wf.template.Printer.FrmPrintTemplate;


import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.spire.doc.*;
import com.spire.doc.Table;
import com.spire.doc.collections.SectionCollection;
import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.util.AreaReference;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xwpf.usermodel.*;


import com.spire.doc.documents.*;
import com.spire.doc.packages.*;

import com.spire.doc.collections.SectionCollection;

import com.spire.doc.Document;
import com.spire.doc.DocumentObject;
import com.spire.doc.Section;
import com.spire.doc.documents.DocumentObjectType;
import com.spire.doc.documents.Paragraph;
import com.spire.doc.fields.TextRange;
import com.fasterxml.jackson.core.type.TypeReference;
import com.fasterxml.jackson.databind.ObjectMapper;

public class OfficerHelper {
    public FrmPrintTemplate HisFrmPrintTemplate = null;
    /**
     * 数据源
     */
    private DB HisDB = null;
    private String FrmID = null;

    public void MakeDocFile(String frmID, String templateFile, FrmPrintTemplate template, long workID) throws Exception {
        this.FrmID = frmID;
        this.HisFrmPrintTemplate = template;
        //生成数据源.
        this.HisDB = new DB(template.getFrmID(), template.getName(), template.getMyPK(), workID);

        DBEles ss = this.HisDB.HisDBEles;
        DBDtls ss2 = this.HisDB.HisDBDtls;
        Document sdoc = new Document();
        sdoc.loadFromFile(templateFile);
        // 新的表单检测
        DataItemClass mainTags = getMainTags(sdoc);
        for (ItemClass item : mainTags.getTagItems()) {
            if (item.type == MyitemEnum.TagInline) {
                // 文本中字段赋值
                setTagInlineValue(template, sdoc, item);
            } else if (item.type == MyitemEnum.TagCell) {
                // 表格中字段赋值
                setTagCellValue(template, sdoc, item);
            } else if (item.type== MyitemEnum.Tag) {
                // 块字段赋值
                setTagValue(template, sdoc, item);
            } else if (item.type == MyitemEnum.Table) {
                // 单选字段赋值
                setTagTableValue(template, sdoc, item);
            } else if (item.type== MyitemEnum.ChildTable) {
                // 从表赋值
                setChildTableValue(template, sdoc, item);
            }
        }
        sdoc.saveToFile(templateFile);
    }
    private void setChildTableValue(FrmPrintTemplate template, Document sdoc, ItemClass item) throws Exception {
        Table tagTable = item.tagTable;
        String title = tagTable.getTitle();
        String tableDescription = tagTable.getTableDescription();

        if (!title.isEmpty() && title.startsWith("MetaData$")) {
            String tableName = title.replace("MetaData$", "");
            if (!tableName.isEmpty()) {
                DBDtl dbDtl = this.HisDB.HisDBDtls.GetValByKey(tableName);
                MapDtl dtl = new MapDtl(tableName);
                int vstoEditModel = dtl.getatPara().GetValIntByKey("VSTOEditModel");
                int vstoEditPara = dtl.getatPara().GetValIntByKey("VSTOEditPara");
                int rowNum = 0;
                int groupNum = 0;
                int attrsCount = 0;

                if (!tableDescription.isEmpty()) {
                    String[] strArr = tableDescription.split("\\|");
                    if (strArr.length == 3) {
                        try {
                            rowNum = Integer.parseInt(strArr[0]);
                            groupNum = Integer.parseInt(strArr[1]);
                            attrsCount = Integer.parseInt(strArr[2]);
                        } catch (NumberFormatException e) {
                            // 处理异常
                        }
                    }
                }

                if (dbDtl != null) {
                    if (vstoEditModel == 0) {
                        DataTable dtable = dbDtl.HisDB;
                        for (int i=0;i<tagTable.getRows().getCount();i++) {
                            TableRow row =tagTable.getRows().get(i);
                            for (int j=0;j<row.getCells().getCount();j++) {
                                TableCell cell =row.getCells().get(j);
                                if (cell.getDocumentObjectType() == DocumentObjectType.Structure_Document_Tag_Cell) {
                                    // 子表单元格赋值
                                    setChildTableCellValue2(cell, dtable);
                                }
                            }
                        }
                    } else if (vstoEditModel == 1) {
                        // 从表平铺展示
                        int ppNum = vstoEditPara; // 一行平铺个数
                        int rowNumTemp = rowNum; // 每组数据行数
                        int rowRe = groupNum; // 平铺行数
                        int tRow = rowRe * rowNumTemp; // 表格总行数
                        // 处理逻辑
                    } else if (vstoEditModel == 2) {
                        DataTable dtable = dbDtl.HisDB;
                        // 获取从表的子表
                        MapDtls mapDtlsChilds = new MapDtls();
                        mapDtlsChilds.Retrieve(MapDtlAttr.FK_MapData, tableName);

                        // 暂时只用一个，碰到多个时再说
                        String childDtlNo = "";
                        String childDtlName = "";
                        for (int j=0;j<mapDtlsChilds.ToJavaList().size();j++) {
                            MapDtl mapDtlChild = mapDtlsChilds.ToJavaList().get(j);
                            childDtlNo = mapDtlChild.getNo();
                            childDtlName = mapDtlChild.getName();
                        }

                        // 从表子表数据
                        DataTable childDt = new DataTable();
                        if ((childDtlNo.isEmpty() && dtable != null) || dtable.Rows.size() > 0) {
                            // OID RefPK
                            StringBuilder oids = new StringBuilder("-1");
                            for (DataRow dr : dtable.Rows) {
                                String tOid = dr.get("OID").toString();
                                oids.append(",").append(tOid);
                            }
                            if (!oids.toString().equals("-1")) {
                                childDt = DBAccess.RunSQLReturnTable("SELECT * FROM " + childDtlNo + " WHERE RefPK IN(" + oids + ") ORDER BY Idx ASC");
                            }
                        }

                        // 从表的子表字段属性
                        MapAttrs mapAttrsChilds = new MapAttrs(childDtlNo);
                        List<MapAttr> attrList2 = mapAttrsChilds.ToJavaList();
                        int cAttrsCount = attrList2.size(); // 子表字段个数
                        int ppNum = vstoEditPara; // 一行平铺个数
                        int rowNumTemp = rowNum; // 每组数据行数
                        int rowRe = groupNum; // 平铺行数
                        int tRow = rowRe * rowNumTemp; // 表格总行数
                        int tCol = attrsCount * ppNum; // 表格总列数

                        // 从表字段
                        int sRow = 1; // 开始行号
                        for (int re = 1; re <= rowRe; re++) {
                            sRow = 1 + (re - 1) * (rowNumTemp + attrsCount); // 行偏移计算
                            int eRow = sRow + attrsCount - 1;

                            // 从表字段
                            for (int r = sRow; r <= eRow; r++) {
                                for (int ce = 1; ce <= ppNum; ce++) {
                                    int opcR = r;
                                    int opC = 1 + (ce - 1);
                                    TableCell cell = tagTable.get(opcR - 1, opC);
                                    if (cell.getDocumentObjectType() == DocumentObjectType.Structure_Document_Tag_Cell) {
                                        // 子表单元格赋值
                                        setChildTableCellValue2(cell, dtable);
                                    }
                                }
                            }

                            // 从表子表填充
                            int zopR = eRow + 1;
                            for (int zr = zopR; zr <= (zopR + rowNumTemp - 2); zr++) {
                                int zopC = cAttrsCount * ppNum + 1;
                                for (int col = 1; col <= zopC; col++) {
                                    try {
                                        TableCell cell = tagTable.get(zr, col);
                                        if (cell.getDocumentObjectType() == DocumentObjectType.Structure_Document_Tag_Cell) {
                                            // 从表子表单元格赋值
                                            setChildTableCellValue2(cell, childDt);
                                        }
                                    } catch (Exception e) {
                                        // 处理异常
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
    }
    private void setChildTableCellValue2(DocumentObject cellObj, DataTable dtable) {
        if (cellObj.getDocumentObjectType() == DocumentObjectType.Structure_Document_Tag_Cell) {
            StructureDocumentTagCell item = (StructureDocumentTagCell) cellObj;
            String tag = item.getSDTProperties().getTag();
            if (tag == null || tag.contains("MetaCol$")) return;
            int txtType = 0;
            String name = "";
            if (!tag.contains("|Value")) {
                if (!tag.contains("|Data") && !tag.contains("|Time")) {
                    return;
                } else {
                    if (tag.contains("|Data")) {
                        name = tag.replace("MetaData$", "").replace("|Data", "");
                        txtType = 1;
                    }
                    if (tag.contains("|Time")) {
                        name = tag.replace("MetaData$", "").replace("|Time", "");
                        txtType = 2;
                    }
                }
            } else {
                name = tag.replace("MetaData$", "").replace("|Value", "");
            }
            String[] arr = name.split("\\.");
            if (arr.length == 3) {
                String rowIndex = arr[1];
                String keyOfEn = arr[2];
                int sRow = Integer.parseInt(rowIndex) - 1;
                if (sRow < 0) return;
                String val = getDataValue(dtable, keyOfEn, Integer.parseInt(rowIndex) - 1);
                if (!val.isEmpty()) {
                    if (txtType == 0) {
                        if (item.getSDTProperties().getSDTType() == SdtType.Text) {
                            if (item.getParagraphs().getCount() == 1) {
                                item.getParagraphs().get(0).setText(val);
                            }
                        }
                        if (item.getSDTProperties().getSDTType() == SdtType.Drop_Down_List) {
                            SdtDropDownList stList = (SdtDropDownList) item.getSDTProperties().getControlProperties();
                            String displayValue = "";
                            for (int j=0;j<stList.getListItems().getCount();j++) {
                                    SdtListItem it = stList.getListItems().get(j);
                                if (it.getValue().equals(val)) {
                                    displayValue = it.getDisplayText();
                                    break;
                                }
                            }
                            if (!displayValue.isEmpty()) {
                                if (item.getParagraphs().getCount() == 1) {
                                    item.getParagraphs().get(0).setText(displayValue);
                                }
                            }
                        }
                        if (item.getSDTProperties().getSDTType() == SdtType.Date_Picker) {
                            if (item.getParagraphs().getCount() == 1) {
                                String[] arrTime = val.split(" ");
                                item.getParagraphs().get(0).setText(arrTime[0]);
                            }
                        }
                    } else if (txtType == 1) {
                        if (item.getSDTProperties().getSDTType() == SdtType.Date_Picker) {
                            if (item.getParagraphs().getCount() == 1) {
                                String[] arrTime = val.split(" ");
                                item.getParagraphs().get(0).setText(arrTime[0]);
                            }
                        }
                    } else if (txtType == 2) {
                        String[] arrTime = val.split(" ");
                        if (arrTime.length == 2) {
                            SdtDropDownList stList = (SdtDropDownList) item.getSDTProperties().getControlProperties();
                            String displayValue = "";
                            for (int j=0;j<stList.getListItems().getCount();j++) {
                                SdtListItem it = stList.getListItems().get(j);
                                if (it.getDisplayText().equals(arrTime[1])) {
                                    displayValue = it.getValue();
                                    break;
                                }
                            }
                            if (!displayValue.isEmpty()) {
                                if (item.getParagraphs().getCount() == 1) {
                                    item.getParagraphs().get(0).setText(arrTime[1]);
                                }
                            }
                        }
                    }
                } else {
                    if (item.getParagraphs().getCount() == 1) {
                        item.getParagraphs().get(0).setText("");
                    }
                }
            }
        }
    }
    private String getDataValue(DataTable dt, String colName, int rowIndex) {
        String result = "";

        if (dt != null && dt.Rows.size() > 0) {
            if (rowIndex < dt.Rows.size()) {
                // Idx 会出现不保存序号 都是0 按照行取值
                List<DataRow> drs0 = dt.select("Idx=0");
                if (drs0.size() == dt.Rows.size()) {
                    result = dt.Rows.get(rowIndex).get(colName).toString();
                } else {
                    List<DataRow> drs = dt.select("Idx='" + rowIndex + "'");
                    if (drs.size() > 0) {
                        result = drs.get(0).get(colName).toString();
                    }
                }
            }
        }

        return result;
    }
    private void setTagTableValue(FrmPrintTemplate template, Document sdoc, ItemClass item) {
        Table tagTable = item.tagTable;
        String descr = tagTable.getTableDescription();

        if (tagTable.getRows().getCount() == 1 && !descr.isEmpty()) {
            try {
                ObjectMapper objectMapper = new ObjectMapper();
                List<EnumClass> list = objectMapper.readValue(descr, new TypeReference<List<EnumClass>>() {});
                if (!list.isEmpty()) {
                    String ctlKeyOfEn = item.keyname;
                    String val = this.HisDB.HisDBEles.GetValByKey(template.getFrmID(), ctlKeyOfEn).AttrValue.toString();
                    EnumClass ctlEnumClass = null;
                    for (EnumClass enumClass : list) {
                        if (enumClass.No.equals(val) || enumClass.Lab.equals(val)) {
                            ctlEnumClass = enumClass;
                            break;
                        }
                    }
                    if (ctlEnumClass != null) {
                        for (int i=0;i<item.tagTable.getRows().getCount();i++) {
                            TableRow row = item.tagTable.getRows().get(i);
                            for (int j=0;j<row.getCells().getCount();j++) {
                                TableCell cell = row.getCells().get(j);
                                if (cell.getDocumentObjectType() == DocumentObjectType.Table_Cell) {
                                    for (int g=0;g<cell.getChildObjects().getCount();g++) {
                                        DocumentObject cellObj = cell.getChildObjects().get(g);
                                        if (cellObj.getDocumentObjectType() == DocumentObjectType.Paragraph) {
                                            for (int k=0;k<((Paragraph) cellObj).getChildObjects().getCount();k++) {
                                                DocumentObject pobj =((Paragraph) cellObj).getChildObjects().get(k);
                                                if (pobj.getDocumentObjectType() == DocumentObjectType.Structure_Document_Tag_Inline) {
                                                    StructureDocumentTagInline tagInline = (StructureDocumentTagInline) pobj;
                                                    boolean isChecked = checkBoxCheck(ctlKeyOfEn, ctlEnumClass, tagInline);
                                                    // if (isChecked) {
                                                    //     return;
                                                    // }
                                                }
                                            }
                                        } else if (cellObj.getDocumentObjectType() == DocumentObjectType.Structure_Document_Tag_Inline) {
                                            StructureDocumentTagInline tagInline = (StructureDocumentTagInline) cellObj;
                                            boolean isChecked = checkBoxCheck(ctlKeyOfEn, ctlEnumClass, tagInline);
                                            // if (isChecked) {
                                            //     return;
                                            // }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            } catch (Exception ex) {
                // 处理异常
                ex.printStackTrace();
            }
        }
    }
    private boolean checkBoxCheck(String ctlKeyOfEn, EnumClass ctlEnumClass, StructureDocumentTagInline cBox) {
        boolean ifCheck = false;
        if (cBox.getSDTProperties().getSDTType() == SdtType.Check_Box) {
            String tag = cBox.getSDTProperties().getTag();

            if (tag.startsWith("CheckBoxItem$")) {
                String ctlStr = tag.replace("CheckBoxItem$", "");
                String[] ctlArr = ctlStr.split("\\.");

                if (ctlArr.length == 2) {
                    String ctlKey = ctlArr[0];
                    String ctlNo = ctlArr[1];
                    if (ctlKey.equals(ctlKeyOfEn)) {
                        SdtCheckBox stCheck = (SdtCheckBox) cBox.getSDTProperties().getControlProperties();

                        if (ctlNo.equals(ctlEnumClass.No)) {
                            stCheck.setChecked(true);
                            ifCheck = true;
                        } else {
                            stCheck.setChecked(false);
                        }
                    }
                }
            }
        }

        return ifCheck;
    }


    private class EnumClass
    {
        public String No ;
        public String Lab;
    }
    private void setTagValue(FrmPrintTemplate template, Document sdoc, ItemClass item) {
        StructureDocumentTag tag = item.tag;
        String label = tag.getSDTProperties().getAlias();
        String tagValue = tag.getSDTProperties().getTag();
        // CheckBoxItem$YangPinMingChen.0
        if (!tagValue.contains("MetaData$")) return;
        if (tagValue.contains("|Title")) return;
        int txtType = 0;
        String ctlKeyOfEn = "";
        if (!tagValue.contains("|Value")) {
            if (!tagValue.contains("|Data") && !tagValue.contains("|Time")) {
                return;
            } else {
                if (tagValue.contains("|Data")) {
                    ctlKeyOfEn = tagValue.replace("MetaData$", "").replace("|Data", "");
                    txtType = 1;
                }
                if (tagValue.contains("|Time")) {
                    ctlKeyOfEn = tagValue.replace("MetaData$", "").replace("|Time", "");
                    txtType = 2;
                }
            }
        } else {
            ctlKeyOfEn = tagValue.replace("MetaData$", "").replace("|Value", "");
        }
        // 编号错误跳过
        if (ctlKeyOfEn.isEmpty()) return;
        String val = this.HisDB.HisDBEles.GetValByKey(template.getFrmID(), ctlKeyOfEn).AttrValue.toString();
        if (val == null || val.isEmpty()) {
            TextRange rt = new TextRange(sdoc);
            rt.setText(" ");
            tag.getChildObjects().add(rt);
        } else {
            if (txtType == 0) {
                if (tag.getSDTProperties().getSDTType() == SdtType.Text) {
                    if (tag.getChildObjects().getCount() == 1) {
                        DocumentObject cellObj = tag.getChildObjects().get(0);
                        if (cellObj.getDocumentObjectType() == DocumentObjectType.Paragraph) {
                            Paragraph pobj = (Paragraph) cellObj;
                            pobj.setText(val);
                        }
                    }
                }
                if (tag.getSDTProperties().getSDTType() == SdtType.Drop_Down_List) {
                    SdtDropDownList stList = (SdtDropDownList) tag.getSDTProperties().getControlProperties();
                    String v = "";
                    for (int j=0;j<stList.getListItems().getCount();j++) {
                            SdtListItem it = stList.getListItems().get(j);
                        if (it.getDisplayText().equals(val)) {
                            v = it.getValue();
                            break;
                        }
                    }
                    if (tag.getChildObjects().getCount() == 1) {
                        DocumentObject cellObj = tag.getChildObjects().get(0);
                        if (cellObj.getDocumentObjectType() == DocumentObjectType.Paragraph) {
                            Paragraph pobj = (Paragraph) cellObj;
                            pobj.setText(val);
                        }
                    }
                }
                if (tag.getSDTProperties().getSDTType() == SdtType.Date_Picker) {
                    String[] arrTime = val.split(" ");
                    if (tag.getChildObjects().getCount() == 1) {
                        DocumentObject cellObj = tag.getChildObjects().get(0);
                        if (cellObj.getDocumentObjectType() == DocumentObjectType.Paragraph) {
                            Paragraph pobj = (Paragraph) cellObj;
                            pobj.setText(arrTime[0]);
                        }
                    }
                }
            } else if (txtType == 1) {
                String[] arrTime = val.split(" ");
                if (tag.getSDTProperties().getSDTType() == SdtType.Date_Picker) {
                    if (tag.getChildObjects().getCount() == 1) {
                        DocumentObject cellObj = tag.getChildObjects().get(0);
                        if (cellObj.getDocumentObjectType() == DocumentObjectType.Paragraph) {
                            Paragraph pobj = (Paragraph) cellObj;
                            pobj.setText(arrTime[0]);
                        }
                    }
                }
            } else if (txtType == 2) {
                String[] arrTime = val.split(" ");
                if (arrTime.length == 2) {
                    SdtDropDownList stList = (SdtDropDownList) tag.getSDTProperties().getControlProperties();
                    String v = "";
                    for (int j=0;j<stList.getListItems().getCount();j++) {
                        SdtListItem it = stList.getListItems().get(j);
                        if (it.getDisplayText().equals(arrTime[1])) {
                            v = it.getValue();
                            break;
                        }
                    }
                    if (!v.isEmpty()) {
                        if (tag.getChildObjects().getCount() == 1) {
                            DocumentObject cellObj = tag.getChildObjects().get(0);
                            if (cellObj.getDocumentObjectType() == DocumentObjectType.Paragraph) {
                                Paragraph pobj = (Paragraph) cellObj;
                                pobj.setText(arrTime[1]);
                            }
                        }
                    }
                }
            }
        }
    }
    private void setTagCellValue(FrmPrintTemplate template, Document sdoc, ItemClass item) {
        StructureDocumentTagCell tagCell = item.tagCell;
        String label = tagCell.getSDTProperties().getAlias();
        String tag = tagCell.getSDTProperties().getTag();
        // CheckBoxItem$YangPinMingChen.0
        if (!tag.contains("MetaData$")) return;
        if (tag.contains("|Title")) return;
        int txtType = 0;
        String ctlKeyOfEn = "";
        if (!tag.contains("|Value")) {
            if (!tag.contains("|Data") && !tag.contains("|Time")) {
                return;
            } else {
                if (tag.contains("|Data")) {
                    ctlKeyOfEn = tag.replace("MetaData$", "").replace("|Data", "");
                    txtType = 1;
                }
                if (tag.contains("|Time")) {
                    ctlKeyOfEn = tag.replace("MetaData$", "").replace("|Time", "");
                    txtType = 2;
                }
            }
        } else {
            ctlKeyOfEn = tag.replace("MetaData$", "").replace("|Value", "");
        }
        // 编号错误跳过
        if (ctlKeyOfEn.isEmpty()) return;
        String val = this.HisDB.HisDBEles.GetValByKey(template.getFrmID(), ctlKeyOfEn).AttrValue.toString();
        if (val == null || val.isEmpty()) {
            TextRange rt = new TextRange(sdoc);
            rt.setText(" ");
            tagCell.getChildObjects().add(rt);
        } else {
            if (txtType == 0) {
                if (tagCell.getSDTProperties().getSDTType() == SdtType.Text) {
                    if (tagCell.getChildObjects().getCount()== 1) {
                        DocumentObject cellObj = tagCell.getChildObjects().get(0);
                        if (cellObj.getDocumentObjectType() == DocumentObjectType.Paragraph) {
                            Paragraph pobj = (Paragraph) cellObj;
                            pobj.setText(val);
                        }
                    }
                }
                if (tagCell.getSDTProperties().getSDTType() == SdtType.Drop_Down_List) {
                    SdtDropDownList stList = (SdtDropDownList) tagCell.getSDTProperties().getControlProperties();
                    String v = "";
                    for (int j=0;j<stList.getListItems().getCount();j++) {
                        SdtListItem it = stList.getListItems().get(j);
                        if (it.getDisplayText().equals(val)) {
                            v = it.getValue();
                            break;
                        }
                    }
                    if (tagCell.getChildObjects().getCount() == 1) {
                        DocumentObject cellObj = tagCell.getChildObjects().get(0);
                        if (cellObj.getDocumentObjectType() == DocumentObjectType.Paragraph) {
                            Paragraph pobj = (Paragraph) cellObj;
                            pobj.setText(val);
                        }
                    }
                }
                if (tagCell.getSDTProperties().getSDTType() == SdtType.Date_Picker) {
                    String[] arrTime = val.split(" ");
                    if (tagCell.getChildObjects().getCount() == 1) {
                        DocumentObject cellObj = tagCell.getChildObjects().get(0);
                        if (cellObj.getDocumentObjectType() == DocumentObjectType.Paragraph) {
                            Paragraph pobj = (Paragraph) cellObj;
                            pobj.setText(arrTime[0]);
                        }
                    }
                }
            } else if (txtType == 1) {
                String[] arrTime = val.split(" ");
                if (tagCell.getSDTProperties().getSDTType() == SdtType.Date_Picker) {
                    if (tagCell.getChildObjects().getCount() == 1) {
                        DocumentObject cellObj = tagCell.getChildObjects().get(0);
                        if (cellObj.getDocumentObjectType() == DocumentObjectType.Paragraph) {
                            Paragraph pobj = (Paragraph) cellObj;
                            pobj.setText(arrTime[0]);
                        }
                    }
                }
            } else if (txtType == 2) {
                String[] arrTime = val.split(" ");
                if (arrTime.length == 2) {
                    SdtDropDownList stList = (SdtDropDownList) tagCell.getSDTProperties().getControlProperties();
                    String v = "";
                    for (int u=0;u<stList.getListItems().getCount();u++) {
                        SdtListItem it = stList.getListItems().get(u);
                        if (it.getDisplayText().equals(arrTime[1])) {
                            v = it.getValue();
                            break;
                        }
                    }
                    if (!v.isEmpty()) {
                        if (tagCell.getChildObjects().getCount() == 1) {
                            DocumentObject cellObj = tagCell.getChildObjects().get(0);
                            if (cellObj.getDocumentObjectType() == DocumentObjectType.Paragraph) {
                                Paragraph pobj = (Paragraph) cellObj;
                                pobj.setText(arrTime[1]);
                            }
                        }
                    }
                }
            }
        }
    }
    private void setTagInlineValue(FrmPrintTemplate template, Document sdoc, ItemClass item) {
        StructureDocumentTagInline tagInline = item.taginline;
        String label = tagInline.getSDTProperties().getAlias();
        String tag = tagInline.getSDTProperties().getTag();
        String text = tagInline.getSDTContent().getText();
        // CheckBoxItem$YangPinMingChen.0
        if (!tag.contains("MetaData$")) return;
        if (tag.contains("|Title")) return;
        int txtType = 0;
        String ctlKeyOfEn = "";
        if (!tag.contains("|Value")) {
            if (!tag.contains("|Data") && !tag.contains("|Time")) {
                return;
            } else {
                if (tag.contains("|Data")) {
                    ctlKeyOfEn = tag.replace("MetaData$", "").replace("|Data", "");
                    txtType = 1;
                }
                if (tag.contains("|Time")) {
                    ctlKeyOfEn = tag.replace("MetaData$", "").replace("|Time", "");
                    txtType = 2;
                }
            }
        } else {
            ctlKeyOfEn = tag.replace("MetaData$", "").replace("|Value", "");
        }
        // 编号错误跳过
        if (ctlKeyOfEn.isEmpty()) return;
        String val = this.HisDB.HisDBEles.GetValByKey(template.getFrmID(), ctlKeyOfEn).AttrValue.toString();

        if (val == null || val.isEmpty()) {
            tagInline.getChildObjects().clear();
            TextRange rt = new TextRange(sdoc);
            rt.setText(" ");
            tagInline.getChildObjects().clear();
            tagInline.getChildObjects().add(rt);
        } else {
            if (txtType == 0) {
                if (tagInline.getSDTProperties().getSDTType() == SdtType.Text) {
                    if (!val.isEmpty()) {
                        TextRange rt = new TextRange(sdoc);
                        rt.setText(val);
                        tagInline.getChildObjects().clear();
                        tagInline.getChildObjects().add(rt);
                    } else {
                        tagInline.getChildObjects().clear();
                    }
                }
                if (tagInline.getSDTProperties().getSDTType() == SdtType.Drop_Down_List) {
                    SdtDropDownList stList = (SdtDropDownList) tagInline.getSDTProperties().getControlProperties();
                    String v = "";
                    for (int i = 0; i < stList.getListItems().getCount(); i++) {
                        SdtListItem it = stList.getListItems().get(i);
                        if (it.getDisplayText().equals(val)) {
                            v = it.getValue();
                            break;
                        }
                    }
                    if (!v.isEmpty()) {
                        TextRange rt = new TextRange(sdoc);
                        rt.setText(val);
                        tagInline.getChildObjects().clear();
                        tagInline.getChildObjects().add(rt);
                    }
                }
                if (tagInline.getSDTProperties().getSDTType() == SdtType.Date_Picker) {
                    String[] arrTime = val.split(" ");
                    TextRange rt = new TextRange(sdoc);
                    rt.setText(arrTime[0]);
                    tagInline.getChildObjects().clear();
                    tagInline.getChildObjects().add(rt);
                }
            } else if (txtType == 1) {
                String[] arrTime = val.split(" ");
                if (tagInline.getSDTProperties().getSDTType() == SdtType.Date_Picker) {
                    TextRange rt = new TextRange(sdoc);
                    rt.setText(arrTime[0]);
                    tagInline.getChildObjects().clear();
                    tagInline.getChildObjects().add(rt);
                }
            } else if (txtType == 2) {
                String[] arrTime = val.split(" ");
                if (arrTime.length == 2) {
                    SdtDropDownList stList = (SdtDropDownList) tagInline.getSDTProperties().getControlProperties();
                    String v = "";
                    for (int j=0;j<stList.getListItems().getCount();j++ ) {
                        SdtListItem it = stList.getListItems().get(j);
                        if (it.getDisplayText().equals(arrTime[1])) {
                            v = it.getValue();
                            break;
                        }
                    }
                    if (!v.isEmpty()) {
                        TextRange rt = new TextRange(sdoc);
                        rt.setText(arrTime[1]);
                        tagInline.getChildObjects().clear();
                        tagInline.getChildObjects().add(rt);
                    }
                }
            }
        }
    }
    private DataItemClass getMainTags(Document document) {
        DataItemClass tags = new DataItemClass();

        for (int i=0;i<document.getSections().getCount();i++ ) {
            Section section = document.getSections().get(i);
            for (int j=0;j<section.getBody().getChildObjects().getCount();j++ ) {
                DocumentObject obj= section.getBody().getChildObjects().get(j);
                if (obj.getDocumentObjectType() == DocumentObjectType.Paragraph) {
                    for (int g=0;g<((Paragraph) obj).getChildObjects().getCount();g++ ) {
                        DocumentObject pobj =((Paragraph) obj).getChildObjects().get(g);
                        // Spire.Doc.Fields.TextRange
                        if (pobj.getDocumentObjectType() == DocumentObjectType.Structure_Document_Tag_Inline) {
                            ItemClass c_item = getItemObj(pobj);
                            if (c_item != null) {
                                tags.getTagItems().add(c_item);
                            }
                        }
                    }
                }
                if (obj.getDocumentObjectType() == DocumentObjectType.Table) {
                    DataItemClass ditem = getChildTables(obj);
                    if (ditem != null) {
                        for (ItemClass nitem : ditem.getTagItems()) {
                            tags.getTagItems().add(nitem);
                        }
                    }
                }
            }
        }
        return tags;
    }
    private ItemClass getItemObj(DocumentObject dObject) {
        ItemClass item = null;
        if (dObject.getDocumentObjectType() == DocumentObjectType.Structure_Document_Tag) {
            StructureDocumentTag tag = (StructureDocumentTag) dObject;
            if (tag != null && tag.getSDTProperties() != null && !tag.getSDTProperties().getTag().isEmpty()) {
                String title = tag.getSDTProperties().getTag();
                if (title.startsWith("MetaData$")) {
                    String tagStr = title.replace("MetaData$", "");
                    String[] strArr = tagStr.split("\\|");
                    if (strArr.length == 2 && (strArr[1].equals("Value") || strArr[1].equals("Data") || strArr[1].equals("Time"))) {
                        String key = strArr[0];
                        item = new ItemClass(key, MyitemEnum.Tag, null, null, tag, null);
                    }
                }
            }
        } else if (dObject.getDocumentObjectType() == DocumentObjectType.Structure_Document_Tag_Inline) {
            StructureDocumentTagInline tagInline = (StructureDocumentTagInline) dObject;
            if (tagInline != null && tagInline.getSDTProperties() != null && !tagInline.getSDTProperties().getTag().isEmpty()) {
                String title = tagInline.getSDTProperties().getTag();
                if (title.startsWith("MetaData$")) {
                    String tagStr = title.replace("MetaData$", "");
                    String[] strArr = tagStr.split("\\|");
                    if (strArr.length == 2 && (strArr[1].equals("Value") || strArr[1].equals("Data") || strArr[1].equals("Time"))) {
                        String key = strArr[0];
                        item = new ItemClass(key, MyitemEnum.TagInline, tagInline, null, null, null);
                    }
                }
            }
        } else if (dObject.getDocumentObjectType() == DocumentObjectType.Structure_Document_Tag_Cell) {
            StructureDocumentTagCell tagCell = (StructureDocumentTagCell) dObject;
            if (tagCell != null && tagCell.getSDTProperties() != null && !tagCell.getSDTProperties().getTag().isEmpty()) {
                String title = tagCell.getSDTProperties().getTag();
                if (title.startsWith("MetaData$")) {
                    String tagStr = title.replace("MetaData$", "");
                    String[] strArr = tagStr.split("\\|");
                    if (strArr.length == 2 && (strArr[1].equals("Value") || strArr[1].equals("Data") || strArr[1].equals("Time"))) {
                        String key = strArr[0];
                        item = new ItemClass(key, MyitemEnum.TagCell, null, tagCell, null, null);
                    }
                }
            }
        } else if (dObject.getDocumentObjectType() == DocumentObjectType.Table) {
            com.spire.doc.Table tagTable = (com.spire.doc.Table) dObject;
            if (tagTable != null) {
                if (!tagTable.getTitle().isEmpty()) {
                    if (tagTable.getTitle().startsWith("MetaData$")) {
                        String key = tagTable.getTitle().replace("MetaData$", "");
                        item = new ItemClass(key, MyitemEnum.ChildTable, null, null, null, tagTable);
                    }
                    if (tagTable.getTitle().startsWith("CheckBoxData$")) {
                        String key = tagTable.getTitle().replace("CheckBoxData$", "");
                        item = new ItemClass(key, MyitemEnum.Table, null, null, null, tagTable);
                    }
                } else {
                    item = new ItemClass("", MyitemEnum.HaveChild, null, null, null, tagTable);
                }
            }
        }
        return item;
    }
    private DataItemClass getChildTables(DocumentObject dObject) {
        DataItemClass tags = new DataItemClass();
        ItemClass c_item = getItemObj(dObject);
        if (c_item != null) {
            if (c_item.type == MyitemEnum.HaveChild) {
                for (int i = 0; i < c_item.tagTable.getRows().getCount(); i++) {
                    TableRow row =c_item.tagTable.getRows().get(i);
                    for (int j = 0; j < row.getCells().getCount(); j++) {
                        TableCell cell =row.getCells().get(j);
                        if (cell.getDocumentObjectType() == DocumentObjectType.Structure_Document_Tag_Cell ){
                            ItemClass cell_item = getItemObj(cell);
                            if (cell_item != null) {
                                tags.getTagItems().add(cell_item);
                            }
                        } else if (cell.getDocumentObjectType() == DocumentObjectType.Table_Cell) {
                            for (int g=0;g<cell.getChildObjects().getCount();g++) {
                                DocumentObject cellobj = cell.getChildObjects().get(g);
                                if (cellobj.getDocumentObjectType() == DocumentObjectType.Paragraph) {
                                    for (int h=0;h<((Paragraph) cellobj).getChildObjects().getCount();h++) {
                                        DocumentObject pobj =((Paragraph) cellobj).getChildObjects().get(h);
                                        if (pobj.getDocumentObjectType() == DocumentObjectType.Structure_Document_Tag_Inline) {
                                            ItemClass tc_item = getItemObj(pobj);
                                            if (tc_item != null) {
                                                tags.getTagItems().add(tc_item);
                                            }
                                        }
                                    }
                                } else if (cellobj.getDocumentObjectType() == DocumentObjectType.Structure_Document_Tag) {
                                    ItemClass cell_item = getItemObj(cellobj);
                                    if (cell_item != null) {
                                        tags.getTagItems().add(cell_item);
                                    }
                                } else if (cellobj.getDocumentObjectType() == DocumentObjectType.Table) {
                                    DataItemClass ditem = getChildTables(cellobj);
                                    if (ditem != null) {
                                        for (ItemClass nitem : ditem.getTagItems()) {
                                            tags.getTagItems().add(nitem);
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            } else {
                // 主表字段 主表单选字段 从表字段
                tags.getTagItems().add(c_item);
            }
        }

        return tags;
    }

    private class DataItemClass {
        private List<ItemClass> tagItems;
        public DataItemClass() {
            tagItems = new ArrayList<>();
        }
        public List<ItemClass> getTagItems() {
            return tagItems;
        }
        public void setTagItems(List<ItemClass> tagItems) {
            this.tagItems = tagItems;
        }
    }
    private enum MyitemEnum {
        // 主表一般字段
        TagInline(0),
        // 主表单选按钮
        TagCell(1),
        // 主表单选按钮
        Tag(2),
        // 主表单选按钮
        Table(3),
        // 2从表
        ChildTable(4),
        // 有子表
        HaveChild(5);
        private final int value;
        MyitemEnum(int value) {
            this.value = value;
        }
        public int getValue() {
            return value;
        }
    }
    private class ItemClass
    {
        /// <summary>
        /// 初始化对象
        /// </summary>
        /// <param name="_keyname">字段编号</param>
        /// <param name="_type">字段类型   0 主表一般字段 1 主表表格内字段 2 主表单选按钮 3从表  </param>
        /// <param name="_taginline">StructureDocumentTagInline 参数</param>
        /// <param name="_tagCell">StructureDocumentTagCell  参数</param>
        /// <param name="_tagTable">Table  参数</param>
        public ItemClass(String _keyname, MyitemEnum _type, StructureDocumentTagInline _taginline , StructureDocumentTagCell _tagCell , StructureDocumentTag _tag , com.spire.doc.Table _tagTable )
        {
            keyname = _keyname;
            type = _type;
            taginline = _taginline;
            tagCell = _tagCell;
            tag = _tag;
            tagTable = _tagTable;
        }

        /// <summary>
        /// 字段名称
        /// </summary>
        public String keyname;
        /// <summary>
        /// 0 主表一般字段 1 主表单选按钮 2从表
        /// </summary>
        public MyitemEnum type ;
        public StructureDocumentTagInline taginline;
        public StructureDocumentTagCell tagCell ;
        public StructureDocumentTag tag ;
        public com.spire.doc.Table tagTable ;
    }


//    public final void MakeDocFile1(String frmID, String templateFile, FrmPrintTemplate template, long workID) throws Exception {
//        XWPFDocument document = null;
//        InputStream in = null;
//        this.FrmID = frmID;
//        this.HisFrmPrintTemplate = template;
//        //生成数据源.
//        this.HisDB = new DB(template.getFrmID(), template.getName(), template.getMyPK(), workID);
//        try {
//            //获取docx解析对象
//            in = new FileInputStream(templateFile);
//            document = new XWPFDocument(in);
//            List<SDTContentControl> allsdts = WordFillContentControls.extractSDTsFromBody(document);
//            for (SDTContentControl sdt : allsdts) {
//                String title = sdt.getTitle();
//                //主表字段格式： MetaData$Bill_VSTO1.xingming           (frmid.字段key)
//                //从表字段格式：
//                //第一行标题：MetaCol$Bill_VSTO1CongBiao.1.BeiZhu   (frmid从表key.index.字段key 从1开始，1是标题不用处理 从2开始)
//                //第二行开始：MetaData$Bill_VSTO1CongBiao.2.BeiZhu   (frmid从表key.index.字段key 从1开始，1是标题不用处理 从2开始)
//                String tag = sdt.getTag();
//                String content = sdt.getContentText();
////                System.out.println(title + ": " + tag + ": " + content);
//                //主从表字段
//                if (tag.startsWith("MetaData")) {
//                    String[] tagArr = tag.replace("MetaData$", "").split("\\.");
//                    String mapKey = "";
//                    //从表
//                    if (tagArr.length == 3) {
//                        mapKey = tagArr[0] + "_" + tagArr[2];
//                        content = getWordTagDtlsValue(tagArr);
//                    } else {//主表
//                        mapKey = tagArr[0] + "_" + tagArr[1];
//                        content = getWordTagValue(tagArr);
//                    }
//                    //枚举的判断
//                    //枚举的判断
//                    MapAttr attr = new MapAttr();
//                    attr.setMyPK(mapKey);
//                    if (attr.RetrieveFromDBSources() == 1) {
//                        String uibindkey = attr.getUIBindKey();
//                        if (uibindkey != null && !uibindkey.equals("")) {
//                            bp.sys.SysEnums myee = new SysEnums(uibindkey);
//                            for (SysEnum e : myee.ToJavaList()) {
//                                String lab = e.getLab();
//                                int IntKey = e.getIntKey();
//                                if (String.valueOf(IntKey).equals(content)) {
//                                    content = lab;
//                                }
//                            }
//                        }
//                    }
//                } else if (tag.startsWith("MetaCol")) {  //从表标题 不用处理
//
//                }
//                sdt.setContent(content);
//            }
//            FileOutputStream out = new FileOutputStream(templateFile);
//            document.write(out);
//            out.close();
//            document.close();
//        } catch (Exception e) {
//            e.printStackTrace();
//        } finally {
//            try {
//                if (document != null) {
//                    document.close();
//                }
//                if (in != null) {
//                    in.close();
//                }
//            } catch (IOException e) {
//                e.printStackTrace();
//            }
//        }
//    }

    //获取主表字段内容   tag: MetaData$frmid.字段key
//    private String getWordTagValue(String[] tagArr) {
//        try {
//            if (tagArr.length != 2) {
//                return "";
//            }
//            Object v = this.HisDB.HisDBEles.ToJavaList().stream().filter(x -> x.RefFrmID.equals(tagArr[0]) && x.AttrKey.equals(tagArr[1])).findFirst().get().AttrValue;
//            if (v != null) {
//                return v.toString();
//            } else {
//                return "";
//            }
//        } catch (Exception e) {
//            e.printStackTrace();
//        }
//        return "";
//    }

    //获取从表字段内容
    //第一行标题：MetaCol$Bill_VSTO1CongBiao.1.BeiZhu   (frmid从表key.index.字段key 从1开始，1是标题不用处理 从2开始)
    //第二行开始：MetaData$Bill_VSTO1CongBiao.2.BeiZhu   (frmid从表key.index.字段key 从1开始，1是标题不用处理 从2开始)
//    private String getWordTagDtlsValue(String[] tagArr) {
//        try {
//            String no = tagArr[0];
//            int index = Integer.parseInt(tagArr[1]);
//            String fieldKey = tagArr[2];
//            index = index - 2;
//            //获取从表数据
//            DataRowCollection rows = this.HisDB.HisDBDtls.ToJavaList().stream().filter(x -> x.No.equals(no)).findFirst().get().HisDB.Rows;
//            if (rows.size() < index + 1) {
//                return "";
//            }
//            Object v = rows.get(index).get(fieldKey);
//            if (v != null) {
//                return v.toString();
//            } else {
//                return "";
//            }
//        } catch (Exception e) {
//            e.printStackTrace();
//        }
//        return "";
//    }
//




    public final void MakeExcelFile(String frmID, String templateFile, FrmPrintTemplate template, long workID) throws Exception {
        this.FrmID = frmID;
        this.HisFrmPrintTemplate = template;
        Workbook workbook = null;
        FileInputStream file = null;
        try {
            //生成数据源.
            this.HisDB = new DB(template.getFrmID(), template.getName(), template.getMyPK(), workID);
            file = new FileInputStream(templateFile);
            ///#region 1. 打开excel准备工作.
            workbook = new XSSFWorkbook(file);
            Sheet worksheet = workbook.getSheetAt(0); // 获取第一个工作表
            //#endregion 打开excel准备工作.
            //#region 2. 从表及字段处理
            // 通过名称获取命名范围
            List<? extends Name> namedRanges = workbook.getAllNames();
            //获取从表区域  单据id+从表id 例子： Name: "bilL_chumendancongbiao" Ranges:"sheet1!$s$10:$V$20"
            HashMap<String, ArrayList<String>> DtlMap = new HashMap<String, ArrayList<String>>();
            for (Name namedRange : namedRanges) {
                String CellID = namedRange.getNameName();
                if (DataType.IsNullOrEmpty(CellID) == false && !CellID.contains(".")) {
                    String DtlName = CellID;
                    String range = namedRange.getRefersToFormula();
                    String sheet = range.split("!")[0];
                    String DtlRange = range.split("!")[1];
                    String name = DtlName + "@" + DtlRange;
                    ArrayList<String> list = new ArrayList<String>();
                    DtlMap.put(name, list);
                }
            }
            //获取从表中的字段  例子：Name:"Dict_XsTZ2Dict_XsTZ2Dtl1.CanYuRenEmail"  Ranges:"sheet1!$s$10:$s$10"
            //主表，从表字段如果是枚举，单独记录
            HashMap<String, SysEnums> EnumMap = new HashMap<String, SysEnums>();
            for (Name namedRange : namedRanges) {
                String CellID = namedRange.getNameName();
                if (DataType.IsNullOrEmpty(CellID) == false && CellID.contains(".")) {
                    String DtlName = CellID.split("\\.")[0];
                    String key = CellID.split("\\.")[1];
                    String range = namedRange.getRefersToFormula();
                    String sheet = range.split("!")[0];
                    String CellRange = range.split("!")[1];
                    String name = key + "@" + CellRange.split(":")[0];
                    String DtlMapKe = ContainsKey(DtlMap, DtlName);
                    //从表字段
                    if (!DtlMapKe.equals("")) {
                        ArrayList<String> list = new ArrayList<String>();
                        list = DtlMap.get(DtlMapKe);
                        if (list == null) {
                            list = new ArrayList<String>();
                        }
                        list.add(name);
                        DtlMap.put(DtlMapKe, list);
                    }
                    //枚举的判断
                    MapAttr attr = new MapAttr();
                    //从表key的最后 有索引值，需要特殊处理
                    if (DtlMapContainsKey(DtlMap, DtlName)) {
                        attr.setMyPK(DtlName + "_" + key.substring(0, key.length() - 1));
                    } else {
                        attr.setMyPK(DtlName + "_" + key);
                    }

                    if (attr.RetrieveFromDBSources() == 1) {
                        String uibindkey = attr.getUIBindKey();
                        if (uibindkey != null && !uibindkey.equals("")) {
                            bp.sys.SysEnums myee = new SysEnums(uibindkey);
                            EnumMap.put(CellID, myee);
                        }
                    }


                }
            }
            ///#endregion 从表及字段处理.
            ///#region 3. 替换主表数据.
            for (Name namedRange : namedRanges) {
                String cellID = namedRange.getNameName();
                if (DataType.IsNullOrEmpty(cellID) == true || cellID.contains(".") == false) {
                    continue;
                }
                // 找到数据.
                String[] strs = cellID.split("\\.");
                String attrFrmID = strs[0];
                String attrKey = strs[1];
                //不属于从表key
                String DtlMapKe = ContainsKey(DtlMap, attrFrmID);
                if (!DtlMapKe.equals("")) {
                    continue;
                }
                //获取值.
                String val = this.HisDB.HisDBEles.GetValByKey(attrFrmID, attrKey).AttrValue.toString();
                val = EnumKey(EnumMap, cellID, val);
                // 获取命名范围的单元格 例子：Sheet1!$E$43
                String range = namedRange.getRefersToFormula();
                //$E$43
                String cellRange = range.split("!")[1];
                //E
                String cellColindexStr = cellRange.split("\\$")[1];
                int cellColindex = getLetterIndex(cellColindexStr.charAt(0));
                //43
                int cellRowindex = Integer.parseInt(cellRange.split("\\$")[2]) - 1;
                // 获取指定单元格（例如：第一行第一列，即A1）
                Row row = worksheet.getRow(cellRowindex); // 获取行
                Cell cell = row.getCell(cellColindex); // 获取列
                // 输出当前单元格的值
//                System.out.println("当前单元格的值: " + cell.getStringCellValue());
                // 修改单元格的值
                cell.setCellValue(val);
            }

            ///#endregion 2. 替换主表数据.
            ///#region 3. 替换从表数据.
            //循环从表
            for (Map.Entry<String, ArrayList<String>> kvp : DtlMap.entrySet()) {
                // Console.WriteLine($"Key = {kvp.Key}, Value = {kvp.Value}");
                //Dict_XsTZ2Dict_xsTZ2Dtl1@$s$10:$V$20
                String name = kvp.getKey().split("@")[0];
                String sindexStr = kvp.getKey().split("@")[1].split(":")[0];
                String eindexStr = kvp.getKey().split("@")[1].split(":")[1];
                int sindex = Integer.parseInt(sindexStr.split("\\$")[2]);
                int eindex = Integer.parseInt(eindexStr.split("\\$")[2]);
                //获取值.
                DataTable dtable = this.HisDB.HisDBDtls.GetValByKey(name).HisDB;
                ArrayList<String> list = new ArrayList<String>();
                list = DtlMap.get(kvp.getKey());
                //字段重复次数
                int dtlfieldCount = DtlFieldCount(list);
                int vsindex = 1;

                //以列的形式(从上到下)填充从表数据start
//                最大行数
                int scount = (eindex - sindex);
                for (int i = 0; i <= dtlfieldCount; i++) {
                    for (int j = i * scount; j < (i + 1) * scount; j++) {
                        if (j >= dtable.Rows.size()) {
                            break;
                        }
                        String strj = i + 1 + "";
                        for (String str : list) //CanYuRenEmail1@$s$10
                        {
                            String key = str.split("@")[0];
                            //获取从表字段key
                            String nkey = key.substring(0, key.length() - 1);
                            //获取从表字段重复索引值
                            String nindex = key.substring(key.length() - 1);
                            if (!strj.equals(nindex)) {
                                continue;
                            }
                            String vkey = str.split("@")[1].split("\\$")[1];
                            String vindexStr = str.split("@")[1].split("\\$")[2];
                            String value = dtable.Rows.get(j).getValue(nkey).toString();
                            int vindex = Integer.parseInt(vindexStr);
                            value = EnumKey(EnumMap, name + "." + key, value);
                            //列
                            int cellColindex = getLetterIndex(vkey.charAt(0));
                            //行
                            int cellRowindex = vindex + j;
                            // 获取指定单元格（例如：第一行第一列，即A1）
                            Row wrow = worksheet.getRow(cellRowindex); // 获取行
                            Cell cell = wrow.getCell(cellColindex);   // 获取列
                            // 修改单元格的值
                            cell.setCellValue(value);
                        }
                    }
                }
                //以列的形式(从上到下)填充从表数据end


                //以行的形式(从左往右)填充从表数据start
                //根据行数和字段重复次数获取excel中占用的行数
//                int dtlCountFieldCount=dtable.Rows.size()/dtlfieldCount;
//                for(int i=0;i<=dtlCountFieldCount;i++) {
//                    //不能超过选择的总行数
//                    if (vsindex > (eindex - sindex))
//                    {
//                        break;
//                    }
//
//                    for(int j=0;j<dtlfieldCount;j++) {
//                        String strj=j+"";
//                        //从第几行取数据
//                        int rindex=i+i*(dtlfieldCount-1)+j;
//                        //不能超过从表数据行数
//                        if(rindex>dtable.Rows.size()-1)
//                        {
//                            break;
//                        }
//
//                        for (String str : list) //CanYuRenEmail0@$s$10  CanYuRenEmail1@$s$10  CanYuRenEmail2@$s$10
//                        {
//                            String key = str.split("@")[0];
//                            //获取从表字段key
//                            String nkey=key.substring(0,key.length()-1);
//                            //获取从表字段重复索引值
//                            String nindex=key.substring(key.length()-1);
//                            if(!strj.equals(nindex)){
//                                continue;
//                            }
//                            //Console.WriteLine($"Key = {key}"
//                            String vkey = str.split("@")[1].split("\\$")[1];
//                            String vindexStr = str.split("@")[1].split("\\$")[2];
//                            int vindex = Integer.parseInt(vindexStr);
//                            if (eindex >= (vindex + vsindex))
//                            {
////                            value = EnumKey(EnumMap, name + "." + key, value);
//
//                               String value= dtable.Rows.get(rindex).getValue(nkey).toString();
//                                //列
//                                int cellColindex = getLetterIndex(vkey.charAt(0));
//                                //行
//                                int cellRowindex = vindex + vsindex-1;
//                                // 获取指定单元格（例如：第一行第一列，即A1）
//                                Row wrow = worksheet.getRow(cellRowindex); // 获取行
//                                Cell cell = wrow.getCell(cellColindex); // 获取列
//                                value = EnumKey(EnumMap, name+"_"+ nkey, value);
//                                // 修改单元格的值
//                                cell.setCellValue(value);
//                            }
//                        }
//                    }
//                    vsindex = vsindex++;
//                }
                //以行的形式(从左往右)填充从表数据end
            }
            // 保存修改
            FileOutputStream out = new FileOutputStream(templateFile);
            workbook.write(out);
            out.close();
            workbook.close();
        } catch (RuntimeException ex) {
            throw new RuntimeException("err@VSTO模版打印错误:" + ex.getMessage());
        } finally {
            try {
                if (workbook != null) {
                    workbook.close();
                }
                if (file != null) {
                    file.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        ///#endregion 3. 替换从表数据.
    }
    public final String ContainsKey(HashMap<String, ArrayList<String>> DtlMap, String key) {
        for (Map.Entry<String, ArrayList<String>> kvp : DtlMap.entrySet()) {
            // Console.WriteLine($"Key = {kvp.Key}, Value = {kvp.Value}");
            if (kvp.getKey().split("@")[0].equals(key)) {
                return kvp.getKey();
            }
        }
        return "";
    }

    public final boolean DtlMapContainsKey(HashMap<String, ArrayList<String>> DtlMap, String key) {
        // 遍历键
        for (Map.Entry<String, ArrayList<String>> entry : DtlMap.entrySet()) {
            if (entry.getKey().startsWith(key)) {
                return true;
            }
            ;
        }
        return false;
    }

    public final String EnumKey(HashMap<String, SysEnums> EnumMap, String key, String val) {
        //枚举字段
        if (EnumMap.containsKey(key)) {
            SysEnums Enums = EnumMap.get(key);
            String rstr = "";
            for (SysEnum e : Enums.ToJavaList()) {
                String lab = e.getLab();
                int IntKey = e.getIntKey();
                if (String.valueOf(IntKey).equals(val)) {
                    rstr = lab;
                }
            }
            if (rstr.equals("")) {
                return val;
            }
            return rstr;
        } else {
            return val;
        }
    }
    /**
     * excel行平铺模式为实体
     * @param frmID
     * @param templateFile
     * @param template
     * @param no
     * @throws Exception
     */
    public final void MakeExcelFileForEntity(String frmID, String templateFile, FrmPrintTemplate template, String no) throws Exception {
        this.FrmID = frmID;
        this.HisFrmPrintTemplate = template;
        Workbook workbook = null;
        FileInputStream file = null;
        try {

            //生成数据源.
            this.HisDB = new DB(template.getFrmID(), template.getName(), template.getMyPK(), no);
            file = new FileInputStream(templateFile);
            ///#region 1. 打开excel准备工作.
            workbook = new XSSFWorkbook(file);
            // 通过名称获取命名范围
            List<? extends Name> namedRanges = workbook.getAllNames();
            //获取从表区域  单据id+从表id 例子： Name: "bilL_chumendancongbiao" Ranges:"sheet1!$s$10:$V$20"
            HashMap<String, ArrayList<String>> DtlMap = new HashMap<String, ArrayList<String>>();
            for (Name namedRange : namedRanges) {
                String CellID = namedRange.getNameName();
                if (DataType.IsNullOrEmpty(CellID) == false && !CellID.contains(".")) {
                    String DtlName = CellID;
                    String range = namedRange.getRefersToFormula();
                    String sheet = range.split("!")[0];
                    String DtlRange = range.split("!")[1];
                    String name = DtlName + "@" + DtlRange;
                    ArrayList<String> list = new ArrayList<String>();
                    DtlMap.put(name, list);
                }
            }
            //获取从表中的字段  例子：Name:"Dict_XsTZ2Dict_XsTZ2Dtl1.CanYuRenEmail"  Ranges:"sheet1!$s$10:$s$10"
            //主表，从表字段如果是枚举，单独记录
            HashMap<String, SysEnums> EnumMap = new HashMap<String, SysEnums>();
            for (Name namedRange : namedRanges) {
                String CellID = namedRange.getNameName();
                if (DataType.IsNullOrEmpty(CellID) == false && CellID.contains(".")) {
                    String DtlName = CellID.split("\\.")[0];
                    String key = CellID.split("\\.")[1];
                    String range = namedRange.getRefersToFormula();
                    String sheet = range.split("!")[0];
                    String CellRange = range.split("!")[1];
                    String name = key + "@" + CellRange.split(":")[0];
                    String DtlMapKe = ContainsKey(DtlMap, DtlName);
                    //从表字段
                    if (!DtlMapKe.equals("")) {
                        ArrayList<String> list = new ArrayList<String>();
                        list = DtlMap.get(DtlMapKe);
                        if (list == null) {
                            list = new ArrayList<String>();
                        }
                        list.add(name);
                        DtlMap.put(DtlMapKe, list);
                    }
                    //枚举的判断
                    MapAttr attr = new MapAttr();
                    //从表key的最后 有索引值，需要特殊处理
                    if (DtlMapContainsKey(DtlMap, DtlName)) {
                        attr.setMyPK(DtlName + "_" + key.substring(0, key.length() - 1));
                    } else {
                        attr.setMyPK(DtlName + "_" + key);
                    }

                    if (attr.RetrieveFromDBSources() == 1) {
                        String uibindkey = attr.getUIBindKey();
                        if (uibindkey != null && !uibindkey.equals("")) {
                            bp.sys.SysEnums myee = new SysEnums(uibindkey);
                            EnumMap.put(CellID, myee);
                        }
                    }


                }
            }

            int numberOfSheets = workbook.getNumberOfSheets();
            for (int i = 0; i < numberOfSheets; i++) {
                Sheet worksheet = workbook.getSheetAt(i);
                //#endregion 打开excel准备工作.
                //#region 2. 从表及字段处理
                ///#endregion 从表及字段处理.
                ///#region 3. 替换主表数据.
                for (Name namedRange : namedRanges) {

                    int sheetIndex=parseSheetIndexFromReference(namedRange,workbook);
                    if(sheetIndex !=i){
                        continue;
                    }
                    String cellID = namedRange.getNameName();
                    if(cellID.equals("Search_SHRWGLCB.RESULT1")){
                        cellID="Search_ShenHeRenWuGuanLi.SEQUENCE1";
                    }
                    if(cellID.equals("Search_SHRWGLCB.RESULT2")){
                        cellID="Search_ShenHeRenWuGuanLi.SEQUENCE2";
                    }
                    if(cellID.equals("Search_SHRWGLCB.RESULT3")){
                        cellID="Search_ShenHeRenWuGuanLi.SEQUENCE3";
                    }
                    if(cellID.equals("Search_SHRWGLCB.RESULT4")){
                        cellID="Search_ShenHeRenWuGuanLi.SEQUENCE4";
                    }
                    if(cellID.equals("Search_SHRWGLCB.RESULT5")){
                        cellID="Search_ShenHeRenWuGuanLi.SEQUENCE5";
                    }
                    if (DataType.IsNullOrEmpty(cellID) == true || cellID.contains(".") == false) {
                        continue;
                    }
                    // 找到数据.
                    String[] strs = cellID.split("\\.");
                    String attrFrmID = strs[0];
                    String attrKey = strs[1];
                    //不属于从表key
                    String DtlMapKe = ContainsKey(DtlMap, attrFrmID);
                    if (!DtlMapKe.equals("")) {
                        continue;
                    }
                    //获取值.
                    DBEles dbEles=this.HisDB.HisDBEles;
                    String val="";
                    if(dbEles.stream().count()>0){
                        val=dbEles.GetValByKey(attrFrmID, attrKey).AttrValue.toString();
                    }
                    val = EnumKey(EnumMap, cellID, val);

                    if(cellID.contains("Search_ShenHeRenWuGuanLi.SEQUENCE")){
                        val="SN:"+val;
                    }
                    // 获取命名范围的单元格 例子：Sheet1!$E$43
                    String range = namedRange.getRefersToFormula();
                    //$E$43
                    String cellRange = range.split("!")[1];
                    //E
                    String cellColindexStr = cellRange.split("\\$")[1];
                    int cellColindex = getLetterIndex(cellColindexStr.charAt(0));
                    //43
                    int cellRowindex = Integer.parseInt(cellRange.split("\\$")[2]) - 1;
                    // 获取指定单元格（例如：第一行第一列，即A1）
                    Row row = worksheet.getRow(cellRowindex); // 获取行
                    Cell cell = row.getCell(cellColindex); // 获取列
                    cell.setCellValue(val);
                }

                //替换从表数据
                for (Map.Entry<String, ArrayList<String>> kvp : DtlMap.entrySet()) {

                    String name = kvp.getKey().split("@")[0];

                    Name nameRange = workbook.getName(name);
                    int sheetIndex=parseSheetIndexFromReference(nameRange,workbook);
                    if(sheetIndex !=i){
                        continue;
                    }

                    ArrayList<String> list = kvp.getValue();

                    // 1. 找出最大行号
                    int maxRowNum = list.stream()
                            .mapToInt(str -> new CellReference(
                                    str.split("@")[1]).getRow())
                            .max()
                            .orElse(0);

                    // 2. 获取数据表
                    if(DataType.IsNullOrEmpty(this.HisDB.HisDBDtls.GetValByKey(name))){
                        continue;
                    }
                    DataTable dataTable = this.HisDB.HisDBDtls.GetValByKey(name).HisDB;

                    // 3. 处理每一行数据
                    for (DataRow dataRow : dataTable.Rows) {
                        // 移动并创建新行
                        if (worksheet.getLastRowNum() >= maxRowNum + 1) {
                            worksheet.shiftRows(maxRowNum + 1, worksheet.getLastRowNum(), 1);
                        }
                        Row newRow = worksheet.createRow(maxRowNum + 1);

                        // 为所有定义好的列创建单元格
                        for (String str : list) {

                            String[] parts = str.split("@");
                            String colKey = parts[0];
                            String colRef = parts[1];

                            int colIndex = getLetterIndex(colRef.split("\\$")[1].charAt(0));
                            Cell cell = newRow.createCell(colIndex);

                            CellStyle style = workbook.createCellStyle();
                            // 设置边框样式
                            style.setBorderTop(BorderStyle.THIN);
                            style.setBorderBottom(BorderStyle.THIN);
                            style.setBorderLeft(BorderStyle.THIN);
                            style.setBorderRight(BorderStyle.THIN);

                            // 设置边框颜色（黑色）
                            style.setTopBorderColor(IndexedColors.BLACK.getIndex());
                            style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
                            style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
                            style.setRightBorderColor(IndexedColors.BLACK.getIndex());

                            cell.setCellStyle(style);

                            CellReference h6Ref = new CellReference(colRef);
                            int rowIndex = h6Ref.getRow(); // 返回5
                            int lieIndex = h6Ref.getCol(); // 返回7

                            if (colKey.equals("QXDJ")) {
                                int lieSpan= 3;
                                // 1. 创建合并区域（只跨列不跨行）
                                CellRangeAddress mergeRegion = new CellRangeAddress(
                                        newRow.getRowNum(),     // 起始行（当前行）
                                        newRow.getRowNum(),     // 结束行（同一行）
                                        lieIndex,   // 起始列
                                        lieIndex + lieSpan - 1      // 结束列
                                );
                                // 2. 执行合并
                                worksheet.addMergedRegion(mergeRegion);
                                ///创建黑色边框样式
                                CellStyle borderStyle = workbook.createCellStyle();
                                borderStyle.setBorderTop(BorderStyle.THIN);
                                borderStyle.setBorderBottom(BorderStyle.THIN);
                                borderStyle.setBorderLeft(BorderStyle.THIN);
                                borderStyle.setBorderRight(BorderStyle.THIN);
                                borderStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
                                borderStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
                                borderStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
                                borderStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());

                                // 6. 将边框样式应用到合并区域的所有单元格
                                for (int col = lieIndex; col <= lieIndex + 3 - 1; col++) {
                                    Cell cell1 =newRow.getCell(col);
                                    if (cell1 == null) {
                                        cell1 = newRow.createCell(col);
                                    }
                                    cell1.setCellStyle(borderStyle);
                                }
                                cell.setCellValue(String.valueOf(dataRow.getValue(colKey)));

                            }
                            // 设置单元格值
                            if (dataRow.containsKey(colKey)) {
                                cell.setCellValue(String.valueOf(dataRow.getValue(colKey)));
                            }
                        }
                        maxRowNum++;
                    }
                }
            }

            // 保存修改
            FileOutputStream out = new FileOutputStream(templateFile);
            workbook.write(out);
            out.close();
            workbook.close();
        } catch (RuntimeException ex) {
            throw new RuntimeException("err@VSTO模版打印错误:" + ex.getMessage());
        } finally {
            try {
                if (workbook != null) {
                    workbook.close();
                }
                if (file != null) {
                    file.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    public int parseSheetIndexFromReference(Name name, Workbook workbook) {
        try {
            AreaReference areaRef = new AreaReference(
                    name.getRefersToFormula(),
                    workbook.getSpreadsheetVersion()
            );

            CellReference firstCell = areaRef.getFirstCell();
            String sheetName = firstCell.getSheetName();
            return workbook.getSheetIndex(sheetName);
        } catch (Exception e) {
            return -1;
        }
    }

    //判断从表字段重复的次数
    //从表字段会出现重复的情况  例子：   xingming0,dizhi0,xingming1,dizhi1,xingming2,dizhi2,
    private int DtlFieldCount(List<String> list) {
        String f0 = list.get(0).split("@")[0];

        f0 = f0.substring(0, f0.length() - 2);
        int i = 0;
        for (String str : list) {
            String nstr = str.split("@")[0];
            nstr = nstr.substring(0, nstr.length() - 2);
            if (f0.equals(nstr)) {
                i++;
            }
        }
        return i;
    }

    //获取字母在字母表中的序号  从0开始
    private static int getLetterIndex(char letter) {
        if (letter >= 'A' && letter <= 'Z') {
            return letter - 'A'; // 大写字母
        } else if (letter >= 'a' && letter <= 'z') {
            return letter - 'a'; // 小写字母
        } else {
            return -1; // 不是字母
        }
    }
}
